﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using Microsoft.Office.Interop;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
using System.Threading;
using System.Windows.Forms;
namespace MP.Core.Library
{
    public class ExcelHelper
    {
        #region Attributes

        private string filename;
        private string connectionString;

        #endregion

        #region properties

        public string Filename
        {
            get { return filename; }
            set { filename = value; }
        }
        public string ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }

        #endregion

        #region Methods
        public ExcelHelper()
        {
            filename = "";
            connectionString = "";
        }
        public string ExcelConnectString2003()// chuoi ket noi cho excel 2003
        {
            return @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                   @"Data Source={0};" +
                   @"Extended Properties=" + Convert.ToChar(34).ToString() +
                   @"Excel 8.0;HDR=Yes;IMEX=1" + Convert.ToChar(34).ToString();
        }

        public string ExcelConnectString2007()// chuoi ket noi cho excel 2007
        {
            return @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                   @"Data Source={0};" +
                   @"Extended Properties=" + Convert.ToChar(34).ToString() +
                   @"Excel 12.0 Xml;HDR=YES" + Convert.ToChar(34).ToString();
        }
        public void GetConnectionString()// lua chon mot trong hai chuoi ket noi den excel 2003 hay 2007
        {
            string filename_extension = filename;
            if (filename_extension == "")
            {
                return;
            }
            else
            {
                string[] s = filename_extension.Split(Convert.ToChar(46));      //46 ma thap phan cua dau .
                if (s[s.Length - 1] == "xls")
                    connectionString = string.Format(ExcelConnectString2003(), filename);
                else
                    if (s[s.Length - 1] == "xlsx")
                        connectionString = string.Format(ExcelConnectString2007(), filename);
                    else
                        return;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdText"> cau lenh sql</param>
        /// <returns>datatable</returns>
        public DataTable ReadExcel(string cmdText)
        {
            try
            {
                DataTable table = new DataTable();
                GetConnectionString();//lay ve chuoi ket noi excel 
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand(cmdText, conn);
                    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                    adapter.Fill(table);
                    adapter.Dispose();
                    conn.Close();
                }
                return table;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public DataTable ReadExcelBySheet(string SheetName)
        {
            try
            {
                SheetName = SheetName.Replace("'", "");
                string strSQL = "Select * " +
                                "From [{0}$]";
                strSQL = string.Format(strSQL, SheetName);
                return ReadExcel(strSQL);
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        #endregion
        /// <summary>
        /// Lay tat ca cac sheet trong excel
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public DataTable GetAllSheet()
        {
            try
            {
                GetConnectionString();
                using (OleDbConnection connection = new
                          OleDbConnection(connectionString))
                {
                    connection.Open();
                    DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    connection.Close();
                    return schemaTable;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            return null;
        }

    }
}
